package com.java.core.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.sql.TIMESTAMP;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.java.core.utils.DateUtils;

/**
 * Excel简单操作类
 * 
 * @author Mr.Wei
 * 
 */
public class ExcelSimpleHandler extends AbstractExcelHandler {

	public ExcelSimpleHandler() {
	}

	public ExcelSimpleHandler(ExcelModel excelModel) {
		super();
	}

	/**
	 * 导出excel
	 * 
	 * @param excelName
	 *            excel名称
	 * @param values
	 *            参数
	 * @param lists
	 *            列表
	 * @throws IOException
	 */
	public void export(OutputStream out, Map<String, String> params,
			List<Map<String, Object>>... lists) throws IOException {
		if (lists == null || lists.length == 0) {
			return;
		}
		// 创建工作簿
		Workbook workbook = this.createWorkbook();
		for (int sheetIndex = 0; sheetIndex < lists.length; sheetIndex++) {
			// 创建工作表
			Sheet sheet = workbook.createSheet("sheet" + (sheetIndex + 1));
			// 获取参数值
			String title = params.get("title");
			String subtitle = params.get("subtitle");
			String headers = params.get("headers");
			// 获取列
			List<JSONObject> columns = JSON.parseArray(headers,
					JSONObject.class);
			int size = columns.size();
			// 样式
			CellStyle cs = workbook.createCellStyle();
			cs.setBorderBottom(CellStyle.BORDER_THIN);
			cs.setBorderTop(CellStyle.BORDER_THIN);
			cs.setBorderLeft(CellStyle.BORDER_THIN);
			cs.setBorderRight(CellStyle.BORDER_THIN);
			cs.setAlignment(CellStyle.ALIGN_CENTER);
			cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			// 列头行
			Row third = null;
			// 标题行
			if (!StringUtils.isBlank(title)) {
				Row first = sheet.createRow(0);
				first.setHeightInPoints(25);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, size - 1));// 合并单元格
				Cell firstCell = first.createCell(0);
				firstCell.setCellValue(title);
				firstCell.setCellStyle(cs);
				if (!StringUtils.isBlank(subtitle)) {
					// 副标题行
					Row second = sheet.createRow(1);
					second.setHeightInPoints(20);
					Cell secondCell = second.createCell(0);
					secondCell.setCellStyle(cs);
					secondCell.setCellValue(subtitle);
					sheet.addMergedRegion(new CellRangeAddress(1, 1, 0,
							size - 1));

					third = sheet.createRow(2);
				} else {
					third = sheet.createRow(1);
				}
			} else {
				third = sheet.createRow(0);
			}
			// 创建列头
			for (int i = 0; i < size; i++) {
				third.setHeightInPoints(18);
				JSONObject column = columns.get(i);
				String header = column.getString("header");
				Cell cell = third.createCell(i);
				CellStyle headerCs = workbook.createCellStyle();
				headerCs.cloneStyleFrom(cs);
				cell.setCellStyle(headerCs);
				cell.setCellValue(header);
			}
			// 循环列表
			List<Map<String, Object>> list = lists[sheetIndex];
			int rowIndex = 1;
			for (Map<String, Object> map : list) {
				// 创建数据行
				Row row = sheet.createRow(third.getRowNum() + rowIndex);
				// 循环标题头
				for (int i = 0; i < size; i++) {
					JSONObject column = columns.get(i);
					String dataIndex = column.getString("dataIndex");
					Integer width = column.getInteger("width");
					if (width == null) {
						width = 100;
					}
					Cell cell = row.createCell(i);
					// 设置样式
					cell.setCellStyle(cs);
					sheet.setColumnWidth(i, pixel2PoiWidth(width));
					// 赋值
					this.setCellValue(cell, map.get(dataIndex));
				}
				rowIndex++;
			}
		}
		// 写入输出流
		workbook.write(out);
		// 关闭输出流
		out.flush();
		out.close();
	}

	/**
	 * 像素转poi列宽
	 * 
	 * @param pixel
	 * @return
	 */
	private int pixel2PoiWidth(int pixel) {
		return (int) (pixel * 37.5);
	}

	/**
	 * 赋值
	 * 
	 * @param cell
	 * @param value
	 */
	private void setCellValue(Cell cell, Object value) {
		// 根据不同的数据类型赋值
		if (value == null) {
			cell.setCellValue("");
		} else if (value instanceof Date) {// Date
			cell.setCellValue(DateUtils.format((Date) value, "yyyy-MM-dd"));
		} else if (value instanceof Boolean) {// Boolean
			cell.setCellValue((Boolean) value);
		} else if (value instanceof Integer) {// Integer
			cell.setCellValue((Integer) value);
		} else if (value instanceof Long) {// Long
			cell.setCellValue((Long) value);
		} else if (value instanceof Double) {// Double
			cell.setCellValue((Double) value);
		} else if (value instanceof Float) {// Float
			cell.setCellValue((Float) value);
		} else if (value instanceof TIMESTAMP) {// oracle的TIMESTAMP类型
			TIMESTAMP timestamp = (TIMESTAMP) value;
			try {
				Date date = timestamp.dateValue();
				cell.setCellValue(DateUtils.format(date, "yyyy-MM-dd"));
			} catch (SQLException e) {
			}
		} else {// 其他类型统一转换为字符串
			cell.setCellValue(String.valueOf(value));
		}
	}

}
